﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;
using System.Data;
using System.Drawing;

namespace TRIG_MOD_STATUSSUMMARY
{

    class DataForStatusSummary
    {
        #region "Properties & Variable"

        List<Users> userDetails = new List<Users>();
        //List<Users> userDetails = new List<Users>();
        public GridView grid;
        SPSite oSite;
        SPWeb oWeb;
        public DataTable dtResult = new DataTable();

        public List<Users> olstuser = new List<Users>();

        public string Group { get; set; }
        public string Types { get; set; }

        public string stDT { get; set; }
        public string endDT { get; set; }
        public RadioButtonList rdDates { get; set; }

        public string cpStDT { get; set; }
        public string cpEndDT { get; set; }
        public string lpStDt { get; set; }
        public string lpEndDt { get; set; }

        public string cmlQry { get; set; }
        public int Allcnt { get; set; }

        #region "Others"

        public int TtlCnt { get; set; }
        public int TtlDiscipCnt { get; set; }
        public int AppCnt { get; set; }
        public int ACmtsCnt { get; set; }
        public int NAppCnt { get; set; }
        public int UCNTRvCnt { get; set; }
        public int UCNSRvCnt { get; set; }
        public int UCLNRvCnt { get; set; }
        public int CNTDraft { get; set; }

        public int UWYQRvCnt { get; set; }
        public int SupersededCnt { get; set; }
        public int SentbkToCNTCnt { get; set; }


        public int TtlSubmittoCNSCnt { get; set; }
        public int TtlSubmittoCLNCnt { get; set; }
        public int TtlSendbacktoCNSCnt { get; set; }

        public int TtlSubmittedCnt = 0;
        public int TtlApprovedCnt = 0;
        public int TtlACmtsCnt = 0;
        public int TtlNAppCnt = 0;
        public int TtlUCNTRvCnt = 0;
        public int TtlUCNSRvCnt = 0;
        public int TtlUCLNRvCnt = 0;
        public int TtlUWYQRvCnt = 0;
        public int Ttlcol1Sum = 0;
        public int Ttlcol2Sum = 0;
        public int SubmittoCNSCnt = 0;
        public int SubmittoCLNCnt = 0;
        public int SendbacktoCNSCnt = 0;
        public int ttlSupersededCnt = 0;
        public int ttlSentbkToCNTCnt = 0;
        public int ttlCNTDraftcnt = 0;

        public int col1Sum { get; set; }
        public int col2Sum { get; set; }
        public double colsPer { get; set; }
        public int ckCnt = 0;
        DataTable dts = null;
        int cnt;

        #endregion

        public int ttlSubmittoCNScpCnt { get; set; }
        public int ttlSubmittoCNSppCnt { get; set; }
        public int ttlvary_periods { get; set; }
        public int ttlcolCnt { get; set; }

        public int SubmittoCNScpCnt = 0;
        public int SubmittoCNSppCnt = 0;

        #endregion


        public void GridCSS(GridView grid)
        {
            grid.Width = Unit.Percentage(75);
            grid.GridLines = GridLines.Horizontal;
            grid.HeaderStyle.CssClass = "ms-vh2";
            grid.CellPadding = 2;
            grid.BorderWidth = Unit.Pixel(5);
            grid.HeaderStyle.Font.Bold = true;
            grid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        }

        public void CreateTable()
        {
            dtResult = CreateDataColumns("Discipline(s)", "Total Submitted", "Approved", "Approved with Comments", "Not Approved", "CNT Review", "CNS Review", "CLN Review", "% of Method Statement Approved & Approved as noted");
        }

        // DocTypeStatusSummaryCNT
        public void CreateTable(string contractor)
        {
            string docType = "";
            if (contractor == "contractor")
            {
                dtResult = CreateDataColumns("Discipline(s)", "Total Submitted", "Approved", "Approved with Comments", "Not Approved", "Sent back to CNT", "Under WYQ Review", "Superseded", "Total", "% of Method Statement Approved & Approved as noted");
            }
            else if (contractor == "DS")
            // DrawingSubmittalStatusSummaryCNT
            {
                dtResult = CreateDataColumns("Discipline(s)", "Total Deliverable Submitted", "Client ENDR Signed Off", "Client ENDR U/R", "Approved",
                  "Approved with Comments", "Not Approved", "Sent back to CNT", "Under WYQ Review", "Superseded", "Total", "% of Method Statement Approved & Approved as noted");
            }
            else if (contractor == "IR_CNT" || contractor == "IRS_CNT")
            {
                if (contractor == "IRS_CNT") docType = "Document Type"; else docType = "Discipline(s)";
                dtResult = CreateDataColumns(docType, "Total Submitted", "Approved", "Approved with Comments", "Not Approved", "Sent back to CNT", "Under WYQ Review", "Superseded", "Total");
            }
            else if (contractor == "IR_CNS" || contractor == "IRS_CNS")
            {
                if (contractor == "IRS_CNS") docType = "Document Type"; else docType = "Discipline(s)";
                if (Types == "CNT")
                    dtResult = CreateDataColumns(docType, "Total Submitted", "Draft", "CNS Review", "CLN Review", "Total");
                else
                    dtResult = CreateDataColumns(docType, "Total Submitted", "CNS Review", "CLN Review", "Total");
            }
            else
            {
                dtResult = CreateDataColumns("Discipline(s)", "Total Submitted", "SEG", " * Current Period", " * Previous Period", " * Change", "Total");
            }
        }

        public DataTable DynamicColumns()
        {
            // Define the new datatable
            DataTable dt = new DataTable();
            // Define 3 columns
            DataColumn dc;
            dc = new DataColumn("| BagID |");
            dt.Columns.Add(dc);
            dc = new DataColumn("Weight |");
            dt.Columns.Add(dc);
            dc = new DataColumn("ArticlesCount |");
            dt.Columns.Add(dc);
            return dt;
        }

        public DataTable DynamicRows(object dynamicDataTable)
        {
            // Use Existing DataTable
            DataTable dt = (DataTable)dynamicDataTable;

            //Add Rows
            DataRow row = dt.NewRow();
            row["| BagID |"] = "| BagID |";
            row["Weight |"] = "Weight |";
            row["ArticlesCount |"] = "ArticlesCount |";
            dt.Rows.Add(row);
            return dt;
        }

        public static DataTable CreateDataColumns(params string[] colna)
        {
            DataTable dt = new DataTable();
            foreach (string item in colna)
            {
                dt.Columns.Add(item);
            }
            return dt;
        }

        // Bind and upload to grid
        public void GetDocItemsbyDiscipiline(string lstName, List<string> discipItems, Label errorMessage, string contractor)
        {
            if (contractor == "DR") { olstuser.Clear(); GetSecEng(); }

            foreach (string discipItem in discipItems)
            {
                if (contractor == "consultant")
                {
                    GetListItems(lstName, errorMessage, discipItem, "consultant");
                }
                else if (contractor == "contractor")
                {
                    //GetListItems_DSS(lstName, errorMessage, discipItem, "contractor");
                    GetListItems_DSS(lstName, errorMessage, discipItem);
                }
                else if (contractor == "DS")
                {
                    GetListItems(lstName, errorMessage, discipItem, "DS");
                }
                else if (contractor == "DR")
                {
                    GetListItems_DR(lstName, errorMessage, discipItem);// grid.Visible = true; }
                }
                else if (contractor == "IR")
                {
                    GetListItems_IR(lstName, errorMessage);// grid.Visible = true; }
                }
                else { }
            }

        }

        public int GetDocItemsbyDiscipiline(string lstName, Label errorMessage, string contractor)
        {
            int ckCnt = 0;
            if (contractor == "IR")
            {
                DocStatus_IR("ckData");
                ckCnt = GetItems(lstName, cmlQry);
                if (ckCnt > 0)
                {
                    GetListItems_IR(lstName, errorMessage);
                }
                else
                { errorMessage.Text = "No Records Found"; errorMessage.ForeColor = System.Drawing.Color.Green; grid.Visible = false; }
            }
            else { }
            return ckCnt;
        }

        public int GetDocItemsbyDiscipiline_MM(string lstName, List<string> discipItems, Label errorMessage, string contractor)
        {
            int ckCnt = 0;
            if (contractor == "MM")
            {
                DocStatus_MM("ckData", "");
                ckCnt = GetItems(lstName, cmlQry);
                if (ckCnt > 0)
                {
                    foreach (string discipItem in discipItems)
                    {
                        GetListItems_MM(lstName, errorMessage, discipItem);
                    }
                }
                else { errorMessage.Text = "No Records Found"; errorMessage.ForeColor = System.Drawing.Color.Green; grid.Visible = false; }
            }
            else { }
            return ckCnt;
        }

        public int GetDocItemsbyDiscipiline_DS(string lstName, List<string> discipItems, Label errorMessage, string contractor)
        {
            int ckCnt = 0;
            if (contractor == "DS")
            {
                DocStatus_MM("ckData", "");
                ckCnt = GetItems(lstName, cmlQry);
                if (ckCnt > 0)
                {
                    foreach (string discipItem in discipItems)
                    {
                        GetListItems_DS(lstName, errorMessage, discipItem);
                    }
                }
                else { errorMessage.Text = "No Records Found"; errorMessage.ForeColor = System.Drawing.Color.Green; grid.Visible = false; }
            }
            else { }
            return ckCnt;
        }

        public int GetDocItemsbyDiscipiline_DSS(string lstName, List<string> discipItems, Label errorMessage, string contractor)
        {
            int ckCnt = 0;
            if (contractor == "DSS")
            {
                DocStatus_MM("ckData", "");
                ckCnt = GetItems(lstName, cmlQry);
                if (ckCnt > 0)
                {
                    foreach (string discipItem in discipItems)
                    {
                        GetListItems_DSS(lstName, errorMessage, discipItem);
                    }
                }
                else { errorMessage.Text = "No Records Found"; errorMessage.ForeColor = System.Drawing.Color.Green; grid.Visible = false; }
            }
            else { }
            return ckCnt;
        }

        public int ckItemsInDocs(string lstName)
        {
            //int ckCnt = 0;

            if (string.IsNullOrEmpty(rdDates.SelectedValue))
            {
                cmlQry = "<Where><IsNotNull><FieldRef Name=\"LinkFilenameNoMenu\" /></IsNotNull></Where>";
            }
            else if (Convert.ToInt32(rdDates.SelectedItem.Value) == 0)
            {
                cmlQry = "<Where><And><IsNotNull><FieldRef Name=\"LinkFilenameNoMenu\" /></IsNotNull><Eq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Eq></And></Where>";
            }
            else
            {
                cmlQry = "<Where><And><IsNotNull><FieldRef Name=\"LinkFilenameNoMenu\" /></IsNotNull><And><Geq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></Where>";
            }

            return ckCnt = GetItems(lstName, cmlQry);
        }

        public void GetListItems(string lstName, Label lblError, string discipItem, string contractor)
        {
            try
            {

                DocStatus("ttlDiscip", discipItem);
                TtlDiscipCnt = GetItems(lstName, cmlQry);
                string ttlDiscipcnt = CheckNull(TtlDiscipCnt);
                //if (TtlDiscipCnt > 0)
                //{

                DocStatus("Approved", discipItem);
                AppCnt = GetItems(lstName, cmlQry);
                string a = CheckNull(AppCnt);

                DocStatus("Approved with Comments", discipItem);
                ACmtsCnt = GetItems(lstName, cmlQry);
                string b = CheckNull(ACmtsCnt);

                DocStatus("Not Approved", discipItem);
                NAppCnt = GetItems(lstName, cmlQry);
                string c = CheckNull(NAppCnt);

                string d = "0";
                string e = "0";
                string f = "0";
                if (contractor == "consultant")
                {
                    DocStatus("UnderCNTReview", discipItem);
                    UCNTRvCnt = GetItems(lstName, cmlQry);
                    d = CheckNull(UCNTRvCnt);

                    DocStatus("UnderCNSReview", discipItem);
                    UCNSRvCnt = GetItems(lstName, cmlQry);
                    e = CheckNull(UCNSRvCnt);

                    DocStatus("UnderCLNReview", discipItem);
                    UCLNRvCnt = GetItems(lstName, cmlQry);
                    f = CheckNull(UCLNRvCnt);
                }

                string g = "0";
                if (contractor == "contractor" || contractor == "DS")
                {
                    DocStatus("SubmittedtoCNS", discipItem);
                    SubmittoCNSCnt = GetItems(lstName, cmlQry);
                    g = CheckNull(SubmittoCNSCnt);
                }

                string h = "0";
                string i = "0";
                if (contractor == "DS")
                {
                    DocStatus("SubmittedtoCLN", discipItem);
                    SubmittoCLNCnt = GetItems(lstName, cmlQry);
                    h = CheckNull(SubmittoCLNCnt);

                    DocStatus("SendBacktoCNS", discipItem);
                    SendbacktoCNSCnt = GetItems(lstName, cmlQry);
                    i = CheckNull(SendbacktoCNSCnt);
                }


                //string ttl = a + b + c + d + e + f;

                col1Sum = AppCnt + ACmtsCnt;  // K6
                col2Sum = AppCnt + ACmtsCnt + NAppCnt; // l6 add Superseded extra
                double s;
                if (col1Sum != 0)
                    s = (double)col1Sum / (double)TtlDiscipCnt;
                else s = 0;
                colsPer = Convert.ToInt32(s) * 100; // =K6/B6*100

                if (contractor == "consultant")
                {
                    dtResult.Rows.Add(discipItem, TtlDiscipCnt, a, b, c, d, e, f, String.Format("{0:0.00}", colsPer));
                }
                else if (contractor == "contractor")
                {
                    dtResult.Rows.Add(discipItem, TtlDiscipCnt, a, b, c, g, String.Format("{0:0.00}", colsPer));
                }
                else if (contractor == "DS")
                {
                    dtResult.Rows.Add(discipItem, TtlDiscipCnt, h, i, a, b, c, g, String.Format("{0:0.00}", colsPer));
                }
                else { }

                SetFooterGrid(Convert.ToInt32(TtlDiscipCnt), Convert.ToInt32(AppCnt), Convert.ToInt32(ACmtsCnt), Convert.ToInt32(NAppCnt), Convert.ToInt32(UCNTRvCnt),
                Convert.ToInt32(UCNSRvCnt), Convert.ToInt32(UCLNRvCnt), Convert.ToInt32(col1Sum), Convert.ToInt32(col2Sum), Convert.ToInt32(SubmittoCNSCnt),
                Convert.ToInt32(SubmittoCLNCnt), Convert.ToInt32(SendbacktoCNSCnt));
                // dtResult.Rows.Add(lstName, SubmitCnt, InPrsCnt, AppCnt, RejCnt, WebName);
                //}
            }
            catch (Exception ee)
            {
                // throw;
                lblError.Text = lstName + ":" + ee.Message;
            }
        }

        public void GetListItems_DS(string lstName, Label lblError, string discipItem)
        {

            try
            {
                DocStatus_MM("All", discipItem); TtlDiscipCnt = GetItems(lstName, cmlQry); string ttlDiscipcnt = CheckNull(TtlDiscipCnt);
                if (Group == "CNT")
                {
                    DocStatus_MM("Approved", discipItem); AppCnt = GetItems(lstName, cmlQry); string a = CheckNull(AppCnt);

                    DocStatus_MM("Approved with Comments", discipItem); ACmtsCnt = GetItems(lstName, cmlQry); string b = CheckNull(ACmtsCnt);

                    DocStatus_MM("Not Approved", discipItem); NAppCnt = GetItems(lstName, cmlQry); string c = CheckNull(NAppCnt);

                    DocStatus_MM("UnderWYQReview", discipItem); UWYQRvCnt = GetItems(lstName, cmlQry); string d = CheckNull(UWYQRvCnt);

                    DocStatus_MM("SentBackToCNT", discipItem); SentbkToCNTCnt = GetItems(lstName, cmlQry); string e = CheckNull(SentbkToCNTCnt);

                    DocStatus_MM("Superseded", discipItem); SupersededCnt = GetItems(lstName, cmlQry); string f = CheckNull(SupersededCnt);

                    DocStatus_MM("Submitted to CLN", discipItem); SubmittoCLNCnt = GetItems(lstName, cmlQry); string g = CheckNull(SubmittoCLNCnt);

                    DocStatus_MM("Send Back to CNS", discipItem); SendbacktoCNSCnt = GetItems(lstName, cmlQry); string h = CheckNull(SendbacktoCNSCnt);

                    col1Sum = AppCnt + ACmtsCnt;  // K6
                    col2Sum = AppCnt + ACmtsCnt + NAppCnt + SupersededCnt; // l6 add Superseded extra
                    double s;
                    if (col1Sum != 0)
                        s = (double)col1Sum / (double)TtlDiscipCnt;
                    else s = 0;
                    colsPer = s * 100; // =K6/B6*100

                    int ttlTrans = AppCnt + ACmtsCnt + NAppCnt + UWYQRvCnt + SupersededCnt;
                    dtResult.Rows.Add(discipItem, TtlDiscipCnt, h, g, a, b, c, e, d, f, ttlTrans, String.Format("{0:0.00}", colsPer));

                    SetFooterGrids(TtlDiscipCnt, AppCnt, ACmtsCnt, NAppCnt, SentbkToCNTCnt, UWYQRvCnt, SupersededCnt, 0, SubmittoCLNCnt, col1Sum, col2Sum, SendbacktoCNSCnt, 0);

                }

            }
            catch (Exception ee)
            {
                // throw;
                lblError.Text = lstName + ":" + ee.Message;
                grid.Visible = false;
            }

        }

        public void GetListItems_DSS(string lstName, Label lblError, string discipItem)
        {

            try
            {
                DocStatus_MM("All", discipItem); TtlDiscipCnt = GetItems(lstName, cmlQry); string ttlDiscipcnt = CheckNull(TtlDiscipCnt);
                if (Group == "CNT")
                {
                    DocStatus_MM("Approved", discipItem); AppCnt = GetItems(lstName, cmlQry); string a = CheckNull(AppCnt);

                    DocStatus_MM("Approved with Comments", discipItem); ACmtsCnt = GetItems(lstName, cmlQry); string b = CheckNull(ACmtsCnt);

                    DocStatus_MM("Not Approved", discipItem); NAppCnt = GetItems(lstName, cmlQry); string c = CheckNull(NAppCnt);

                    DocStatus_MM("UnderWYQReview", discipItem); UWYQRvCnt = GetItems(lstName, cmlQry); string d = CheckNull(UWYQRvCnt);

                    DocStatus_MM("SentBackToCNT", discipItem); SentbkToCNTCnt = GetItems(lstName, cmlQry); string e = CheckNull(SentbkToCNTCnt);

                    DocStatus_MM("Superseded", discipItem); SupersededCnt = GetItems(lstName, cmlQry); string f = CheckNull(SupersededCnt);


                    col1Sum = AppCnt + ACmtsCnt;  // K6
                    col2Sum = AppCnt + ACmtsCnt + NAppCnt + SupersededCnt; // l6 add Superseded extra
                    double s;
                    if (col1Sum != 0)
                        s = (double)col1Sum / (double)TtlDiscipCnt;
                    else s = 0;
                    colsPer = s * 100; // =K6/B6*100

                    dtResult.Rows.Add(discipItem, TtlDiscipCnt, a, b, c, e, d, f, TtlDiscipCnt, String.Format("{0:0.00}", colsPer));

                    SetFooterGrids(TtlDiscipCnt, AppCnt, ACmtsCnt, NAppCnt, SentbkToCNTCnt, UWYQRvCnt, SupersededCnt, 0, 0, col1Sum, col2Sum, 0, 0);

                }


                string cnsReview = "0";
                string clnReview = "0";

                if (Group == "CNS")
                {
                    DocStatus_MM("Submitted to CNS", discipItem); UCNSRvCnt = GetItems(lstName, cmlQry); cnsReview = CheckNull(UCNSRvCnt);

                    DocStatus_MM("Submitted to CLN", discipItem); UCLNRvCnt = GetItems(lstName, cmlQry); clnReview = CheckNull(UCLNRvCnt);

                    dtResult.Rows.Add(discipItem, TtlDiscipCnt, cnsReview, clnReview, UCNSRvCnt + UCLNRvCnt);

                    SetFooterGrids(TtlDiscipCnt, 0, 0, 0, 0, 0, 0, UCNSRvCnt, UCLNRvCnt, 0, 0, 0, 0);
                }


            }
            catch (Exception ee)
            {
                // throw;
                lblError.Text = lstName + ":" + ee.Message;
                grid.Visible = false;
            }

        }

        public void GetListItems_DR(string lstName, Label lblError, string discipItem)
        {
            lblError.Text = "";
            DocStatus_DR("ttlDiscip", discipItem, "");
            TtlDiscipCnt = GetItems(lstName, cmlQry);
            string ttlDiscipcnt = CheckNull(TtlDiscipCnt);

            //GetItemsByUserNames(lstName, discipItem);
            GetItemsBySecEngnr(lstName, discipItem);
        }

        public string DocStatus_DR(string status, string discipiline, string user)
        {

            if (status == "cntBetDtsCP")
            {
                cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq>" +
                    "<Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></Where>";
            }

            else if (status == "cntBetDtsPP")
            {   // Total Submittted
                cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(lpStDt) + "</Value></Geq>" +
                    "<Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(lpEndDt) + "</Value></Leq></And></Where>";
            }

            else if (status == "ttlDiscip")
            {   // Total Submittted
                cmlQry = "<Where><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></Where>";
            }

            else if (status == "CNS_CP")
            {   // Total Submittted
                cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Eq>" +
                    "<FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CNS</Value></Eq><And><Eq><FieldRef Name=\"ApprovalStatus\" />" +
                    "<Value Type=\"Lookup\">NA</Value></Eq><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value>" +
                    "</Geq><Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And>" +
                    "</And></Where>";
            }
            else if (status == "CNS_PP")
            {   // Total Submittted
                cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Eq>" +
                    "<FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CNS</Value></Eq><And><Eq><FieldRef Name=\"ApprovalStatus\" />" +
                    "<Value Type=\"Lookup\">NA</Value></Eq><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(lpStDt) + "</Value>" +
                    "</Geq><Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(lpEndDt) + "</Value></Leq></And></And></And>" +
                    "</And></Where>";
            }


            else if (status == "SubmittedtoCNS_CP")   // Under WYQ Review Previos Period
            {
                cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CNS</Value></Eq><And><Eq><FieldRef Name=\"ApprovalStatus\" />" +
                    "<Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"SecEng\" /><Value Type=\"Text\">" + user + "</Value></Eq><And><Eq>" +
                    "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></And></Where>";
            }
            else if (status == "SubmittedtoCNS_PP")   // Under WYQ Review Previos Period
            {
                cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CNS</Value></Eq><And><Eq><FieldRef Name=\"ApprovalStatus\" />" +
                    "<Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"SecEng\" /><Value Type=\"Text\">" + user + "</Value></Eq><And><Eq>" +
                    "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(lpStDt) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(lpEndDt) + "</Value></Leq></And></And></And></And></And></Where>";
            }

            else { }

            return cmlQry;
        }

        public void GetListItems_MM(string lstName, Label lblError, string discipItem) // Material and Method Statement Summary
        {
            try
            {
                DocStatus_MM("All", discipItem); TtlDiscipCnt = GetItems(lstName, cmlQry); string ttlDiscipcnt = CheckNull(TtlDiscipCnt);
                if (Group == "CNT")
                {
                    DocStatus_MM("Approved", discipItem); AppCnt = GetItems(lstName, cmlQry); string a = CheckNull(AppCnt);

                    DocStatus_MM("Approved with Comments", discipItem); ACmtsCnt = GetItems(lstName, cmlQry); string b = CheckNull(ACmtsCnt);

                    DocStatus_MM("Not Approved", discipItem); NAppCnt = GetItems(lstName, cmlQry); string c = CheckNull(NAppCnt);

                    DocStatus_MM("UnderWYQReview", discipItem); UWYQRvCnt = GetItems(lstName, cmlQry); string d = CheckNull(UWYQRvCnt);

                    DocStatus_MM("SentBackToCNT", discipItem); SentbkToCNTCnt = GetItems(lstName, cmlQry); string e = CheckNull(SentbkToCNTCnt);

                    DocStatus_MM("Superseded", discipItem); SupersededCnt = GetItems(lstName, cmlQry); string f = CheckNull(SupersededCnt);

                    dtResult.Rows.Add(discipItem, ttlDiscipcnt, a, b, c, e, d, f, ttlDiscipcnt);

                    SetFooterGrids(TtlDiscipCnt, AppCnt, ACmtsCnt, NAppCnt, SentbkToCNTCnt, UWYQRvCnt, SupersededCnt, 0, 0, 0, 0, 0, 0);

                }
                string cnsReview = "0";
                string clnReview = "0";
                string cntReview = "0";
                if (Group == "CNS")
                {
                    if (Types == "CNT")
                    { DocStatus_MM("Draft", discipItem); CNTDraft = GetItems(lstName, cmlQry); cntReview = CheckNull(UCNSRvCnt); }

                    DocStatus_MM("Submitted to CNS", discipItem); UCNSRvCnt = GetItems(lstName, cmlQry); cnsReview = CheckNull(UCNSRvCnt);

                    DocStatus_MM("Submitted to CLN", discipItem); UCLNRvCnt = GetItems(lstName, cmlQry); clnReview = CheckNull(UCLNRvCnt);

                    if (Types == "CNT")
                        dtResult.Rows.Add(discipItem, TtlDiscipCnt, CNTDraft, cnsReview, clnReview, UCNSRvCnt + UCLNRvCnt + CNTDraft);
                    else
                        dtResult.Rows.Add(discipItem, TtlDiscipCnt, cnsReview, clnReview, UCNSRvCnt + UCLNRvCnt);

                    SetFooterGrids(TtlDiscipCnt, 0, 0, 0, 0, 0, 0, UCNSRvCnt, UCLNRvCnt, 0, 0, 0, CNTDraft);
                }


            }
            catch (Exception x)
            {

                lblError.Text = "No Records found";
                GetErrorLog(lstName, x);
                grid.Visible = false;
                throw;
            }
        }


        public void GetErrorLog(string lstName, Exception x)
        {
            SPListItem oItem = oWeb.Lists.TryGetList("ErrorLogs").AddItem();
            oItem["Title"] = Convert.ToString(lstName);
            oItem["Errors"] = x.Message;
            oItem.Update();
        }

        private string DocStatus_MM(string status, string discipItem)
        {
            #region "As of Today"

            if (rdDates.SelectedItem.Value == "0")
            {
                if (status == "All")
                {
                    cmlQry = "<Where><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq></Where>";
                }
                else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"ApprovalStatus\" />" +
                        "<Value Type=\"Lookup\">" + status + "</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">Closed Out</Value></Eq>" +
                        "<Eq><FieldRef Name=\"Superseded\" /><Value Type=\"Boolean\">0</Value></Eq></And></And></And></Where>";
                }
                else if (status == "Superseded")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Lookup\">Closed Out</Value></Eq><Eq><FieldRef Name=\"Superseded\" /><Value Type=\"Boolean\">1</Value></Eq></And></And></Where>";
                }
                else if (status == "SentBackToCNT")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"DocumentMomentStatus\" />" +
                        "<Value Type=\"Lookup\">Sent Back to CNT</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">Closed Out</Value></Eq></And></And></Where>";
                }
                else if (status == "UnderWYQReview")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Neq><FieldRef Name=\"DocumentMomentStatus\" />" +
                        "<Value Type=\"Lookup\">Sent Back to CNT</Value></Neq><And><Neq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Draft</Value></Neq><Eq>" +
                        "<FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">NA</Value></Eq></And></And></And></Where>";
                }
                else if (status == "Submitted to CNS")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"DocumentMomentStatus\" />" +
                        "<Value Type=\"Lookup\">Submitted to CNS</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">NA</Value></Eq></And></And></Where>";
                }
                else if (status == "Submitted to CLN")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"DocumentMomentStatus\" />" +
                        "<Value Type=\"Lookup\">Submitted to CLN</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">NA</Value></Eq></And></And></Where>";
                }
                else if (status == "Send Back to CNS")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"DocumentMomentStatus\" />" +
                        "<Value Type=\"Lookup\">Send Back to CNS</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">NA</Value></Eq></And></And></Where>";
                }
                else if (status == "Draft")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><Eq><FieldRef Name=\"DocumentMomentStatus\" />" +
                        "<Value Type=\"Lookup\">" + status + "</Value></Eq></And></Where>";
                }
                else if (status == "ckData")
                {
                    cmlQry = "<Where><IsNotNull><FieldRef Name=\"LinkFilenameNoMenu\" /></IsNotNull></Where>";
                }
                //else if (status == "SupersededReview")
                //{ cmlQry = "<Where><Contains><FieldRef Name=\"Document_x0020_Movement_x0020_Status\" /><Value Type=\"Lookup\">CNS</Value></Contains></Where></Query>"; }

                else
                { }
            }
            #endregion

            else
            {
                if (status == "All")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><And><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq><Eq>" +
                        "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq></And></And></Where>";
                }
                else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><And><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq><And><Eq>" +
                        "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"ApprovalStatus\" />" +
                        "<Value Type=\"Lookup\">" + status + "</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">Closed Out</Value></Eq><Eq>" +
                        "<FieldRef Name=\"Superseded\" /><Value Type=\"Boolean\">0</Value></Eq></And></And></And></And></And></Where>";
                }
                else if (status == "Superseded")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">Closed Out</Value></Eq><And><Eq><FieldRef Name=\"Superseded\" />" +
                        "<Value Type=\"Boolean\">1</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Geq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "SentBackToCNT")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Sent Back to CNT</Value></Eq><And><Eq>" +
                        "<FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Lookup\">Closed Out</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" />" +
                        "<Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "UnderWYQReview")
                {
                    cmlQry = "<Where><And><Neq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Sent Back to CNT</Value></Neq><And><Neq>" +
                        "<FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Draft</Value></Neq><And><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Geq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></And></Where>";
                }
                else if (status == "Submitted to CNS")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CNS</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Geq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "Submitted to CLN")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CLN</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Geq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "Send Back to CNS")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Sent back to CNS</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Geq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "Draft")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipItem + "</Value></Eq><And><Eq><FieldRef Name=\"DocumentMomentStatus\" />" +
                        "<Value Type=\"Lookup\">" + status + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" +
                        "" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And>" +
                        "</And></Where></Query>";
                }
                else if (status == "ckData")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></Where>";
                }


                else
                { }
            }
            return cmlQry;
        }

        public void GetListItems_IR(string lstName, Label lblError)
        {
            try
            {
                DocStatus_IR("All"); TtlDiscipCnt = GetItems(lstName, cmlQry); string ttlDiscipcnt = CheckNull(TtlDiscipCnt);
                if (Group == "CNT")
                {
                    DocStatus_IR("Approved"); AppCnt = GetItems(lstName, cmlQry); string a = CheckNull(AppCnt);

                    DocStatus_IR("Approved with Comments"); ACmtsCnt = GetItems(lstName, cmlQry); string b = CheckNull(ACmtsCnt);

                    DocStatus_IR("Not Approved"); NAppCnt = GetItems(lstName, cmlQry); string c = CheckNull(NAppCnt);

                    DocStatus_IR("UnderWYQReview"); UWYQRvCnt = GetItems(lstName, cmlQry); string d = CheckNull(UWYQRvCnt);

                    DocStatus_IR("SentBackToCNT"); SentbkToCNTCnt = GetItems(lstName, cmlQry); string e = CheckNull(SentbkToCNTCnt);

                    DocStatus_IR("Superseded"); SupersededCnt = GetItems(lstName, cmlQry); string f = CheckNull(SupersededCnt);
                    dtResult.Rows.Add(lstName, TtlDiscipCnt, a, b, c, e, d, f, TtlDiscipCnt);
                }
                string cnsReview = "0";
                string clnReview = "0";
                string cntReview = "0";
                if (Group == "CNS")
                {
                    if (Types == "CNT")
                    { DocStatus_IR("Draft"); CNTDraft = GetItems(lstName, cmlQry); cntReview = CheckNull(UCNSRvCnt); }

                    DocStatus_IR("Submitted to CNS"); UCNSRvCnt = GetItems(lstName, cmlQry); cnsReview = CheckNull(UCNSRvCnt);

                    DocStatus_IR("Submitted to CLN"); UCLNRvCnt = GetItems(lstName, cmlQry); clnReview = CheckNull(UCLNRvCnt);

                    if (Types == "CNT")
                        dtResult.Rows.Add(lstName, TtlDiscipCnt, CNTDraft, UCNSRvCnt, UCLNRvCnt, UCNSRvCnt + UCLNRvCnt);
                    else
                        dtResult.Rows.Add(lstName, TtlDiscipCnt, UCNSRvCnt, UCLNRvCnt, UCNSRvCnt + UCLNRvCnt);
                }


            }
            catch (Exception ee)
            {
                // throw;
                lblError.Text = lstName + ":" + ee.Message;
            }
        }

        private string DocStatus_IR(string status)
        {

            if (rdDates.SelectedItem.Value == "0")
            {
                if (status == "All")
                {
                    //cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq>" +
                    //  "<Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></Where>";
                    cmlQry = "<Where><IsNotNull><FieldRef Name=\"Title\" /></IsNotNull></Where>";
                }
                else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Approval_x0020_Status\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Text\">Closed Out</Value></Eq><Eq><FieldRef Name=\"Superseded\" /><Value Type=\"Boolean\">0</Value></Eq></And></And></Where>";
                }
                else if (status == "SentBackToCNT")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">Sent Back to CNT</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Text\">Closed Out</Value></Eq></And></Where>";
                }
                else if (status == "UnderWYQReview")
                {
                    cmlQry = "<Where><And><Neq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">Sent Back to CNT</Value></Neq><And><Neq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" />" +
                        "<Value Type=\"Lookup\">Draft</Value></Neq><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Text\">NA</Value></Eq></And></And></Where>";
                }
                else if (status == "Superseded")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Text\">Closed Out</Value></Eq><Eq><FieldRef Name=\"Superseded\" />" +
                        "<Value Type=\"Boolean\">1</Value></Eq></And></Where>";
                }
                else if (status == "Submitted to CNS")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">Submitted to CNS</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Text\">NA</Value></Eq></And></Where>";
                }
                else if (status == "Submitted to CLN")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">Submitted to CLN</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Text\">NA</Value></Eq></And></Where>";
                }
                else if (status == "Draft")
                {
                    cmlQry = "<Where><Eq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">" + status + "</Value></Eq></Where>";
                }
                else if (status == "ckData")
                {
                    cmlQry = "<Where><IsNotNull><FieldRef Name=\"Title\" /></IsNotNull></Where>";
                }
                //else if (status == "SupersededReview")
                //{ cmlQry = "<Where><Contains><FieldRef Name=\"Document_x0020_Movement_x0020_Status\" /><Value Type=\"Lookup\">CNS</Value></Contains></Where></Query>"; }

                else
                { }
            }
            else
            {
                if (status == "All")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq>" +
                      "<Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></Where>";
                }
                else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><And>" +
                        "<Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq><And><Eq>" +
                        "<FieldRef Name=\"Approval_x0020_Status\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><And><Eq><FieldRef Name=\"Document_x0020_Status\" />" +
                        "<Value Type=\"Text\">Closed Out</Value></Eq><Eq><FieldRef Name=\"Superseded\" /><Value Type=\"Boolean\">0</Value></Eq></And></And></And></And></Where>";
                }
                else if (status == "SentBackToCNT")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">Sent Back to CNT</Value></Eq><And><Eq>" +
                        "<FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Text\">Closed Out</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></Where>";
                }
                else if (status == "UnderWYQReview")
                {
                    cmlQry = "<Where><And><Neq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">Sent Back to CNT</Value></Neq><And><Neq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" />" +
                        "<Value Type=\"Lookup\">Draft</Value></Neq><And><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Text\">NA</Value></Eq><And><Geq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "Superseded")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><And><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq><And><Eq>" +
                        "<FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Text\">Closed Out</Value></Eq><Eq><FieldRef Name=\"Superseded\" /><Value Type=\"Boolean\">1" +
                        "</Value></Eq></And></And></And></Where>";
                }
                else if (status == "Submitted to CNS")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><And>" +
                        "<Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq><And><Eq>" +
                        "<FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Text\">" +
                        "NA</Value></Eq></And></And></And></Where>";
                }
                else if (status == "Submitted to CLN")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><And>" +
                        "<Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq><And><Eq>" +
                        "<FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><Eq><FieldRef Name=\"Document_x0020_Status\" /><Value Type=\"Text\">" +
                        "NA</Value></Eq></And></And></And></Where>";
                }
                else if (status == "Draft")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"Document_x0020_Movement_x0020_St\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></And></Where>";
                }
                else if (status == "ckData")
                {
                    cmlQry = "<Where><And><Geq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpStDT) + "</Value></Geq><Leq>" +
                        "<FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(cpEndDT) + "</Value></Leq></And></Where>";
                }
                //else if (status == "SupersededReview")
                //{ cmlQry = "<Where><Contains><FieldRef Name=\"Document_x0020_Movement_x0020_Status\" /><Value Type=\"Lookup\">CNS</Value></Contains></Where></Query>"; }

                else
                { }

            }
            return cmlQry;
        }

        #region "**************  Master List collection **************"
        //private string DocStatus(string status, string discipiline)
        //{

        //    if (status == "ttlDiscip")

        //    { cmlQry = "<Where><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></Where>"; }
        //    else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
        //    { cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>"; }

        //    else if (status == "UnderCNTReview")
        //    {
        //        cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNT</Value></Contains><And><Eq>" +
        //        "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And>" +
        //        "</And></Where>";
        //    }
        //    else if (status == "UnderCNSReview")
        //    {
        //        cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNS</Value></Contains><And><Eq>" +
        //        "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And>" +
        //        "</And></Where>";
        //    }
        //    else if (status == "UnderCLNReview")
        //    {
        //        cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CLN</Value></Contains><And><Eq>" +
        //        "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And>" +
        //        "</And></Where>";
        //    }

        //    else if (status == "SubmittedtoCNS")   // Under WYQ Review
        //    {
        //        cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>";
        //    }

        //    else if (status == "SubmittedtoCLN")   // Cliend ENDR Under Review
        //    {
        //        cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CLN</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>";
        //    }
        //    else if (status == "SendBacktoCNS")   // Cliend ENDR Signed Off
        //    {
        //        cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Sent back to CNS</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>";
        //    }

        //    else
        //    { }

        //    return cmlQry;
        //}
        #endregion

        private static String ToSPDate(String strDt)
        {
            if (strDt == String.Empty)
                return strDt;
            else
                return (Convert.ToDateTime(strDt)).ToString("yyyy-MM-dd");
        }

        private string DocStatus(string status, string discipiline)
        {

            if (string.IsNullOrEmpty(rdDates.SelectedValue))
            {
                if (status == "ttlDiscip")

                { cmlQry = "<Where><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></Where>"; }
                else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
                { cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>"; }

                else if (status == "UnderCNTReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNT</Value></Contains><And><Eq>" +
                    "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And>" +
                    "</And></Where>";
                }
                else if (status == "UnderCNSReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNS</Value></Contains><And><Eq>" +
                    "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And>" +
                    "</And></Where>";
                }
                else if (status == "UnderCLNReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CLN</Value></Contains><And><Eq>" +
                    "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And>" +
                    "</And></Where>";
                }

                else if (status == "SubmittedtoCNS")   // Under WYQ Review
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>";
                }

                else if (status == "SubmittedtoCLN")   // Cliend ENDR Under Review
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CLN</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>";
                }
                else if (status == "SendBacktoCNS")   // Cliend ENDR Signed Off
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Sent back to CNS</Value></Eq><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq></And></Where>";
                }

                else
                { }
            }

            #region "As of Today"
            else if (Convert.ToInt32(rdDates.SelectedItem.Value) == 0)
            {
                if (status == "ttlDiscip")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" />" +
                      "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Leq></And></Where>";
                }

                else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" />" +
                      "<Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Leq></And></And></Where>";
                }

                else if (status == "UnderCNTReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNT</Value></Contains><And><Neq><FieldRef Name=\"ApprovalStatus\" />" +
                        "<Value Type=\"Lookup\">NA</Value></Neq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" />" +
                            "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Leq></And></And></And></Where>";
                }
                else if (status == "UnderCNSReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNS</Value></Contains><And><Neq><FieldRef Name=\"ApprovalStatus\" />" +
                        "<Value Type=\"Lookup\">NA</Value></Neq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" />" +
                            "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Leq></And></And></And></Where>";
                }
                else if (status == "UnderCLNReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CLN</Value></Contains><And><Neq><FieldRef Name=\"ApprovalStatus\" />" +
                        "<Value Type=\"Lookup\">NA</Value></Neq><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" />" +
                            "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Leq></And></And></And></Where>";
                }

                else if (status == "SubmittedtoCNS")   // Under WYQ Review
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" />" +
                        "<Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value>" +
                        "</Leq></And></And></Where>";
                }

                else if (status == "SubmittedtoCLN")   // Cliend ENDR Under Review
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CLN</Value></Eq><And><Eq>" +
                        "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Leq></And></And></Where>";
                }
                else if (status == "SendBacktoCNS")   // Cliend ENDR Signed Off
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Sent back to CNS</Value></Eq><And><Eq>" +
                        "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Leq></And></And></Where>";
                }

                else
                { }
            }

            #endregion

            #region "Between Dates"

            else if (Convert.ToInt32(rdDates.SelectedItem.Value) == 1)
            {
                if (status == "ttlDiscip")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                    "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" /><Value IncludeTimeValue=\"false\" Type=\"DateTime\">" +
                        ToSPDate(endDT) + "</Value></Leq></And></And></Where>";
                }

                else if (status == "Approved" || status == "Approved with Comments" || status == "Not Approved")
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">" + status + "</Value></Eq><And><Eq>" +
                      "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                      "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                      "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></And></Where>";
                }

                else if (status == "UnderCNTReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNT</Value></Contains><And><Eq>" +
                        "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" />" +
                        "<Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "UnderCNSReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CNS</Value></Contains><And><Eq>" +
                        "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" />" +
                        "<Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></And></And></Where>";
                }
                else if (status == "UnderCLNReview")
                {
                    cmlQry = "<Where><And><Contains><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">CLN</Value></Contains><And><Eq>" +
                        "<FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">NA</Value></Eq><And><Eq><FieldRef Name=\"RefNoDiscipline\" />" +
                        "<Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></And></And></Where>";
                }

                else if (status == "SubmittedtoCNS")   // Under WYQ Review
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"ApprovalStatus\" /><Value Type=\"Lookup\">Submitted to CLN</Value></Eq><And><Eq>" +
                        "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></And></Where>";
                }

                else if (status == "SubmittedtoCLN")   // Cliend ENDR Under Review
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Submitted to CLN</Value></Eq><And><Eq>" +
                        "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></And></Where>";
                }
                else if (status == "SendBacktoCNS")   // Cliend ENDR Signed Off
                {
                    cmlQry = "<Where><And><Eq><FieldRef Name=\"DocumentMomentStatus\" /><Value Type=\"Lookup\">Sent back to CNS</Value></Eq><And><Eq>" +
                        "<FieldRef Name=\"RefNoDiscipline\" /><Value Type=\"Lookup\">" + discipiline + "</Value></Eq><And><Geq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(stDT) + "</Value></Geq><Leq><FieldRef Name=\"Created\" />" +
                        "<Value IncludeTimeValue=\"false\" Type=\"DateTime\">" + ToSPDate(endDT) + "</Value></Leq></And></And></And></Where>";
                }

                else
                { }
            }

            #endregion

            # region "All"

            else
            {

            }

            #endregion
            return cmlQry;
        }

        public int GetItems(string LstTitle, string camlQry)
        {
            SPQuery qry = new SPQuery();
            qry.Query = camlQry;

            using (SPSite oSite = new SPSite(SPContext.Current.Site.Url))
            {
                using (SPWeb oWeb = oSite.OpenWeb())
                {

                    SPList oLst = oWeb.Lists.TryGetList(LstTitle);
                    SPListItemCollection oLstItmColl = oLst.GetItems(qry);
                    cnt = oLstItmColl.Count;
                }
            }
            return cnt;
        }

        protected string CheckNull(int objGrid)
        {
            if (object.Equals(objGrid, 0))
            {
                return "-";
            }
            else
            {
                return objGrid.ToString();
            }
        }

        public static DataTable GetItemsOfSpecificTypeFromWeb(SPWeb oWeb, string camQ, SPListTemplateType ListTemplateType)
        {
            //SPWeb oWeb = oSite.OpenWeb();
            DataTable dt;
            SPSiteDataQuery sQuery;
            sQuery = new SPSiteDataQuery();
            if (ListTemplateType.ToString() == "DocumentLibrary")

                sQuery.Lists = "<Lists ServerTemplate='101' />"; //SPListTemplateType.Tasks
            else if (ListTemplateType.ToString() == "XMLForm")
                sQuery.Lists = "<Lists ServerTemplate='115' />";
            sQuery.ViewFields = camQ;
            sQuery.Webs = "<Webs Scope='SiteCollection'/>";
            dt = oWeb.GetSiteData(sQuery);
            if (dt.Rows.Count == 0)
            { }
            else { }
            return dt;
        }

        private string DocStatus(string status)
        {
            string cmlQry = "";
            if (status == "All")
            { cmlQry = "<FieldRef Name=\"Title\" />"; }
            else if (status == "Approved")
            { cmlQry = "<Where><Eq><FieldRef Name=\"Approval_x0020_Status\" /><Value Type=\"Lookup\">" + status + "</Value></Eq></Where></Query>"; }
            else if (status == "Approved with Comments")
            { cmlQry = "<Where><Eq><FieldRef Name=\"DocStatus\" /><Value Type=\"Lookup\">" + status + "</Value></Eq></Where>"; }
            else if (status == "Not Approved")
            { cmlQry = "<Where><Eq><FieldRef Name=\"DocStatus\" /><Value Type=\"Lookup\">" + status + "</Value></Eq></Where>"; }
            else if (status == "UnderCNTReview")
            { cmlQry = "<Where><Contains><FieldRef Name=\"Document_x0020_Movement_x0020_Status\" /><Value Type=\"Lookup\">CNT</Value></Contains></Where></Query>"; }
            else if (status == "UnderCNSReview")
            { cmlQry = "<Where><Contains><FieldRef Name=\"Document_x0020_Movement_x0020_Status\" /><Value Type=\"Lookup\">CNS</Value></Contains></Where></Query>"; }
            else if (status == "UnderCLNReview")
            { cmlQry = "<Where><Contains><FieldRef Name=\"Document_x0020_Movement_x0020_Status\" /><Value Type=\"Lookup\">CLN</Value></Contains></Where></Query>"; }
            else
            { }

            return cmlQry;
        }

        // Get all Documents and add to generic list and ddlDocsTypes
        // Purpose to get Document Type to anywhere in projects
        public List<string> GetDocumentType(DropDownList ddlDocsTypes)
        {
            List<string> lstItems = new List<string>();
            using (SPSite oSite = new SPSite(SPContext.Current.Site.Url))
            {
                using (oWeb = oSite.OpenWeb())
                {
                    SPList oList = oWeb.Lists.TryGetList("Document Types");
                    SPListItemCollection oLstItemCol = oList.Items;
                    foreach (SPListItem oLstItem in oLstItemCol)
                    {
                        lstItems.Add(Convert.ToString(oLstItem["Title"]));
                        ddlDocsTypes.Items.Add(Convert.ToString(oLstItem["Title"]));
                    }
                }

            }
            return lstItems;
        }

        // Get Discipiline(EL,ME) from Discipiline and bind in Discipiline generic list.
        public List<string> GetDiscipiline()
        {
            List<string> lstItems = new List<string>();
            using (oSite = new SPSite(SPContext.Current.Site.Url))
            {
                using (oWeb = oSite.OpenWeb())
                {
                    SPList oList = oWeb.Lists.TryGetList("Disciplines Master");
                    SPListItemCollection oLstItemCol = oList.Items;
                    foreach (SPListItem oLstItem in oLstItemCol)
                    {
                        lstItems.Add(Convert.ToString(oLstItem["DisciplineAbbrevation"]));
                    }
                }
            }
            return lstItems;
        }

        public void UsersList(Users ouser)
        {
            olstuser.Add(ouser);
        }

        public void GetSecEng()
        {
            SPQuery qry = new SPQuery();
            qry.Query = "<Where><Eq><FieldRef Name=\"Entity_x0020_Lookup\" /><Value Type=\"Lookup\">Contractor</Value></Eq></Where>";
            SPListItemCollection oItemCol = SPContext.Current.Web.Lists.TryGetList("Users Master").GetItems(qry);
            foreach (SPListItem oLstItem in oItemCol)
            {
                Users ouser = new Users();
                ouser.userName = Convert.ToString(oLstItem["User Name"]);
                ouser.firstName = Convert.ToString(oLstItem["First Name"]);
                ouser.lastName = Convert.ToString(oLstItem["Last Name"]);
                UsersList(ouser);
            }
        }

        public void GetItemsBySecEngnr(string lstName, string discipItem)
        {
            
            using (oSite = new SPSite(SPContext.Current.Site.Url))
            {
                using (oWeb = oSite.OpenWeb())
                {
                    SPQuery qry = new SPQuery();
                    qry.Query = "<Where><Eq><FieldRef Name=\"Entity_x0020_Lookup\" /><Value Type=\"Lookup\">Contractor</Value></Eq></Where>";
                    SPList oList = oWeb.Lists.TryGetList("Users Master");
                    SPListItemCollection oLstItemCol = oList.GetItems(qry);
                    int ck = 0;
                    int discipCnt;

                    // Check whether the discipiline count contains values
                    DocStatus_DR("CNS_CP", discipItem, "");
                    discipCnt = GetItems(lstName, cmlQry);
                    DocStatus_DR("CNS_PP", discipItem, "");
                    discipCnt = discipCnt + GetItems(lstName, cmlQry);

                    foreach (var c in olstuser)
                    {
                        DocStatus_DR("SubmittedtoCNS_CP", discipItem, c.userName);
                        SubmittoCNScpCnt = GetItems(lstName, cmlQry);
                        string c_period = CheckNull(SubmittoCNScpCnt);

                        DocStatus_DR("SubmittedtoCNS_PP", discipItem, c.userName);
                        SubmittoCNSppCnt = GetItems(lstName, cmlQry);
                        string p_period = CheckNull(SubmittoCNSppCnt);

                        int vary_periods = (int)SubmittoCNSppCnt - (int)SubmittoCNScpCnt;
                        string vary_period = CheckNull(vary_periods);
                        if (ck == 0)
                        {
                            dtResult.Rows.Add(discipItem, TtlDiscipCnt, c.firstName, c_period, p_period, vary_period, "");
                            SetFooterGrid(TtlDiscipCnt, SubmittoCNScpCnt, SubmittoCNSppCnt, vary_periods);
                            ck++;
                        }
                        else if (ck > 0 && ck < olstuser.Count - 1)
                        {
                            dtResult.Rows.Add("", "", c.firstName, c_period, p_period, vary_period, "");
                            SetFooterGrid(0, SubmittoCNScpCnt, SubmittoCNSppCnt, vary_periods);
                            ck++;
                        }
                        else
                        {
                            if (ck == olstuser.Count) // ck with total user count
                                dtResult.Rows.Add("", "", c.firstName, c_period, p_period, vary_period, "");
                            dtResult.Rows.Add("", "", "", "", "", "", discipCnt);
                            SetFooterGrid(0, SubmittoCNScpCnt, SubmittoCNSppCnt, vary_periods);
                        }
                    }
                }
            }

        }

        #region "DR Old"
        public void GetItemsByUserNames(string lstName, string discipItem)
        {

            using (oSite = new SPSite(SPContext.Current.Site.Url))
            {
                using (oWeb = oSite.OpenWeb())
                {
                    SPQuery qry = new SPQuery();
                    qry.Query = "<Where><Eq><FieldRef Name=\"Entity_x0020_Lookup\" /><Value Type=\"Lookup\">Contractor</Value></Eq></Where>";
                    SPList oList = oWeb.Lists.TryGetList("Users Master");
                    SPListItemCollection oLstItemCol = oList.GetItems(qry);
                    int ck = 0;

                    int discipCnt;


                    // Check whether the discipiline count contains values
                    DocStatus_DR("CNS_CP", discipItem, "");
                    discipCnt = GetItems(lstName, cmlQry);
                    DocStatus_DR("CNS_PP", discipItem, "");
                    discipCnt = discipCnt + GetItems(lstName, cmlQry);



                    foreach (SPListItem oLstItem in oLstItemCol)
                    {
                        Users c = new Users();
                        SPFieldUserValue users = new SPFieldUserValue(oWeb, oLstItem["UserName"].ToString());
                        //c.userName =Convert.ToString(users.User); //Convert.ToString(oLstItem["User Name"]);
                        c.userName = Convert.ToString(oLstItem["User Name"]);
                        c.firstName = Convert.ToString(oLstItem["First Name"]);
                        c.lastName = Convert.ToString(oLstItem["Last Name"]);
                        userDetails.Clear();
                        userDetails.Add(c);

                        DocStatus_DR("SubmittedtoCNS_CP", discipItem, c.userName);
                        SubmittoCNScpCnt = GetItems(lstName, cmlQry);
                        string c_period = CheckNull(SubmittoCNScpCnt);

                        DocStatus_DR("SubmittedtoCNS_PP", discipItem, c.userName);
                        SubmittoCNSppCnt = GetItems(lstName, cmlQry);
                        string p_period = CheckNull(SubmittoCNSppCnt);

                        int vary_periods = (int)SubmittoCNSppCnt - (int)SubmittoCNScpCnt;
                        string vary_period = CheckNull(vary_periods);
                        if (ck == 0)
                        {
                            dtResult.Rows.Add(discipItem, TtlDiscipCnt, c.firstName, c_period, p_period, vary_period, "");
                            SetFooterGrid(TtlDiscipCnt, SubmittoCNScpCnt, SubmittoCNSppCnt, vary_periods);
                            ck++;
                        }
                        else if (ck > 0 && ck < 3)
                        {
                            dtResult.Rows.Add("", "", c.firstName, c_period, p_period, vary_period, "");
                            SetFooterGrid(0, SubmittoCNScpCnt, SubmittoCNSppCnt, vary_periods);
                            ck++;
                        }
                        else
                        {
                            if (ck == 3) // ck with total user count
                                dtResult.Rows.Add("", "", c.firstName, c_period, p_period, vary_period, "");
                            dtResult.Rows.Add("", "", "", "", "", "", discipCnt);
                            SetFooterGrid(0, SubmittoCNScpCnt, SubmittoCNSppCnt, vary_periods);
                        }
                    }
                }
            }

        }
        #endregion

        public class Users
        {
            public string userName { get; set; }
            public string firstName { get; set; }
            public string lastName { get; set; }
        }


        #region "Set footer value"

        public void SetFooterGrid(int s, int a, int b, int c, int d, int e, int f, int g, int h, int i, int j, int k)
        {
            TtlSubmittedCnt = TtlSubmittedCnt + s;
            TtlApprovedCnt = TtlApprovedCnt + a;
            TtlACmtsCnt = TtlACmtsCnt + b;
            TtlNAppCnt = TtlNAppCnt + c;
            TtlUCNTRvCnt = TtlUCNTRvCnt + d;
            TtlUCNSRvCnt = TtlUCNSRvCnt + e;
            TtlUCLNRvCnt = TtlUCLNRvCnt + f;
            Ttlcol1Sum = Ttlcol1Sum + g;
            Ttlcol2Sum = Ttlcol2Sum + h;
            TtlSubmittoCNSCnt = TtlSubmittoCNSCnt + i;
            TtlSubmittoCLNCnt = TtlSubmittoCLNCnt + j;
            TtlSendbacktoCNSCnt = TtlSendbacktoCNSCnt + k;
        }

        private void SetFooterGrid(int a, int b, int c, int d)
        {
            TtlSubmittedCnt = TtlSubmittedCnt + a;
            ttlSubmittoCNScpCnt = ttlSubmittoCNScpCnt + b;
            ttlSubmittoCNSppCnt = ttlSubmittoCNSppCnt + c;
            ttlvary_periods = ttlvary_periods + d;
        }

        public void SetFooterGrids(int ttl, int ap, int abC, int nA, int sBkCNT, int uWYQ, int sup, int subCNS, int subCLN, int col1Sum, int col2Sum, int sBkCNS, int draft)
        {
            TtlSubmittedCnt = TtlSubmittedCnt + ttl;
            TtlApprovedCnt = TtlApprovedCnt + ap;
            TtlACmtsCnt = TtlACmtsCnt + abC;
            TtlNAppCnt = TtlNAppCnt + nA;
            ttlSentbkToCNTCnt = ttlSentbkToCNTCnt + sBkCNT;
            TtlUWYQRvCnt = TtlUWYQRvCnt + uWYQ;
            ttlSupersededCnt = ttlSupersededCnt + sup;

            TtlSubmittoCNSCnt = TtlSubmittoCNSCnt + subCNS;
            TtlSubmittoCLNCnt = TtlSubmittoCLNCnt + subCLN;
            TtlSendbacktoCNSCnt = TtlSendbacktoCNSCnt + sBkCNS;
            ttlCNTDraftcnt = ttlCNTDraftcnt + draft;

            Ttlcol1Sum = Ttlcol1Sum + col1Sum;
            Ttlcol2Sum = Ttlcol2Sum + col2Sum;


        }

        public void FooterSection(Label r1, Label r2, Label r3, Label r4, Label r5)
        {
            double row1 = 100 - ((double)Ttlcol2Sum * 100 / (double)TtlSubmittedCnt);                  // =100-(L10*100/B10)     ** TtlSubmittedCnt(B10)
            double row2 = (100 * ((double)TtlApprovedCnt * 100 / (double)TtlSubmittedCnt)) / 100;      // =100*(C10*100/B10)%    ** TtlApprovedCnt(C10)
            double row3 = (100 * ((double)TtlACmtsCnt * 100 / (double)TtlSubmittedCnt)) / 100;         // =100*(D10*100/B10)%    ** TtlACmtsCnt(D10)
            double row4 = (100 * ((double)TtlNAppCnt * 100 / (double)TtlSubmittedCnt)) / 100;          // =100*(E10*100/B10)%    ** TtlNAppCnt(E10)
            double row5 = (100 * ((double)ttlSupersededCnt * 100 / (double)TtlSubmittedCnt)) / 100;           // =100*(G10*100/B10)%  ** Superseded
            // dtResult.Rows.Add("% of IRs Under Review", row1);
            //dtResult.Rows.Add("% of IRs Approved (Code : 1) as per total submitted", row2);
            //dtResult.Rows.Add("% of IRs Approved as noted (Code : 2) as per total submitted", row3);
            //dtResult.Rows.Add("% of IRs Rejected (Code : 3) as per total submitted", row4);
            // dtResult.Rows.Add("% of IRs Superseded as per total submitted", row5);

            r1.Text = r1.Text + String.Format("{0:0.00}", row1);
            r2.Text = r2.Text + String.Format("{0:0.00}", row2);
            r3.Text = r3.Text + String.Format("{0:0.00}", row3);
            r4.Text = r4.Text + String.Format("{0:0.00}", row4);
            r5.Text = r5.Text + String.Format("{0:0.00}", row5);
        }

        #endregion



        public void BindGrid(GridView gv, Label r1, Label r2, Label r3, Label r4, Label r5, string contractor)
        {
            //double per = (double)TtlSubmittedCnt + (double)Ttlcol1Sum;   // =L10/B10*100
            gv.Visible = true;
            double per = ((double)Ttlcol1Sum / (double)TtlSubmittedCnt) * 100;

            if (contractor == "consultant")
            {
                dtResult.Rows.Add("Total", TtlSubmittedCnt, TtlApprovedCnt, TtlACmtsCnt, TtlNAppCnt, TtlUCNTRvCnt, TtlUCNSRvCnt, TtlUCLNRvCnt, String.Format("{0:0.00}", per));
            }
            else if (contractor == "DSS")
            {
                dtResult.Rows.Add("Total", TtlSubmittedCnt, TtlApprovedCnt, TtlACmtsCnt, TtlNAppCnt, ttlSentbkToCNTCnt, TtlUWYQRvCnt, ttlSupersededCnt, TtlSubmittedCnt, String.Format("{0:0.00}", per));
            }
            else if (contractor == "DS")
            {
                int ttlTrans = TtlApprovedCnt + TtlACmtsCnt + TtlNAppCnt + TtlUWYQRvCnt + ttlSupersededCnt;
                dtResult.Rows.Add("Total", TtlSubmittedCnt, TtlSendbacktoCNSCnt, TtlSubmittoCLNCnt, TtlApprovedCnt, TtlACmtsCnt, TtlNAppCnt, ttlSentbkToCNTCnt, TtlUWYQRvCnt, ttlSupersededCnt, ttlTrans, String.Format("{0:0.00}", per));
            }
            else { }

            FooterSection(r1, r2, r3, r4, r5);
            gv.DataSource = dtResult;
            gv.DataBind();

        }

        public void BindGrid(GridView gv, string webpart)
        {
            gv.Visible = true;
            if (webpart == "DR")
            {
                dtResult.Rows.Add("Total", TtlSubmittedCnt, "SEGS", ttlSubmittoCNScpCnt, ttlSubmittoCNSppCnt, ttlvary_periods, ttlSubmittoCNScpCnt);
            }
            else if (webpart == "IR")
            {
                if (Group == "CNT")
                {
                    dtResult.Rows.Add("Total", TtlDiscipCnt, AppCnt, ACmtsCnt, NAppCnt, SentbkToCNTCnt, UWYQRvCnt, SupersededCnt, TtlDiscipCnt);
                    //dtResult.Rows.Add("Total", TtlSubmittedCnt, TtlApprovedCnt, TtlACmtsCnt, TtlNAppCnt, TtlUWYQRvCnt, ttlSupersededCnt, TtlSubmittedCnt, String.Format("{0:0.00}", per));
                }
                else
                {
                    if (Types == "CNT")
                        dtResult.Rows.Add("Total", TtlDiscipCnt, CNTDraft, UCNSRvCnt, UCLNRvCnt, UCNSRvCnt + UCLNRvCnt);
                    else
                        dtResult.Rows.Add("Total", TtlDiscipCnt, UCNSRvCnt, UCLNRvCnt, UCNSRvCnt + UCLNRvCnt);
                }
            }

            else if (webpart == "MM")
            {
                if (Group == "CNT")
                {
                    dtResult.Rows.Add("Total", TtlSubmittedCnt, TtlApprovedCnt, TtlACmtsCnt, TtlNAppCnt, ttlSentbkToCNTCnt, TtlUWYQRvCnt, ttlSupersededCnt, TtlSubmittedCnt);
                }
                else
                {
                    if (Types == "CNT")
                        dtResult.Rows.Add("Total", TtlSubmittedCnt, ttlCNTDraftcnt, TtlSubmittoCNSCnt, TtlSubmittoCLNCnt, TtlSubmittoCNSCnt + TtlSubmittoCLNCnt + ttlCNTDraftcnt);
                    else
                        dtResult.Rows.Add("Total", TtlSubmittedCnt, TtlSubmittoCNSCnt, TtlSubmittoCLNCnt, TtlSubmittoCNSCnt + TtlSubmittoCLNCnt);
                }
            }

            gv.DataSource = dtResult;
            gv.DataBind();
        }

        
    }
}
